package com.theo.mapper;

import com.theo.entity.CityInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author 方涛
 * @since 2021-02-02
 */
@Repository
public interface CityInfoMapper extends BaseMapper<CityInfo> {

    @Select("SELECT c.city_id,c.city_name FROM `city_info` c\n" +
            "WHERE city_id LIKE CONCAT(SUBSTR(\"100000000\",1,3),\"%\")\n" +
            "AND city_id != \"100000000\" \n" +
            "AND SUBSTR(city_id,6,9)=\"0000\" \n" +
            "AND c.`city_id` NOT IN (\n" +
            "\tSELECT e.city_id FROM `epidemic_info` e\n" +
            "\tWHERE DATE(e.`date`)=#{date}" +
            ") LIMIT 0,6")
    List<CityInfo> findNoDataProvinces(@Param("date")String date);

    @Select("SELECT * FROM city_info \n" +
            "WHERE city_id LIKE CONCAT(SUBSTR(\"100000000\",1,3),\"%\")\n" +
            "AND city_id != \"100000000\" \n" +
            "AND SUBSTR(city_id,6,9)=\"0000\" ")
    List<CityInfo> findProvinces();

    @Select("SELECT city_id,city_name FROM city_info\n" +
            "WHERE city_id LIKE CONCAT(SUBSTR(#{id},1,5),\"%\")\n" +
            "AND city_id != #{id}\n" +
            "AND SUBSTR(city_id,8,9)='00'\n" +
            "ORDER BY city_id")
    List<CityInfo> findCities(Integer id);

    List<CityInfo> findVillages(Integer id);

}
